const sqlite3 = require('sqlite3').verbose();

// Database file path
const dbFilePath = './unihub.db';

// Create a database object
// This will create a file called local.db in the root directory 'back-end'
const db = new sqlite3.Database(dbFilePath);

// Helper function to drop all tables
async function dropAllTables(tables) {
  return new Promise((resolve, reject) => {
    tables.forEach((table, index) => {
      const dropSql = `DROP TABLE IF EXISTS "${table.name}"`;
      db.run(dropSql, (err) => {
        if (err) reject(err);
        else if (index === tables.length - 1) resolve();
      });
    });
  });
}

// Helper function to create a table
async function createTable(sql) {
  // Wrap the database call in a Promise
  return new Promise((resolve, reject) => {
    // Run the SQL query to create the table
    db.run(sql, (err) => {
      if (err) {
        console.error('Error executing SQL:', err);
        reject(err);
      } else {
        resolve();
      }
    });
  });
}

// Function to initialise the database
async function initialiseDatabase() {
  // Define the tables to be created
  const tables = [
    // Define your table here
    //Users table
    {
      name: 'users',
      sql: `CREATE TABLE IF NOT EXISTS users (
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        Username TEXT NOT NULL,
        Email TEXT NOT NULL UNIQUE,
        Password TEXT NOT NULL
      )`
    },

    //Students table
    {
      name: 'students',
      sql: `CREATE TABLE IF NOT EXISTS students (
        StudentID INTEGER NOT NULL, 
        Photo TEXT, 
        Bio TEXT,
        Interests TEXT,
        Courses VARCHAR(255),
        Achievements VARCHAR(255),
        FOREIGN KEY(StudentID) REFERENCES users(ID)
    );`
    },

    //Groups table
    {
      name: 'groups',
      sql: `CREATE TABLE IF NOT EXISTS groups (
        GroupID INTEGER PRIMARY KEY AUTOINCREMENT,
        GroupName TEXT NOT NULL,
        Description TEXT,        
        Type TEXT,
        AdminID INTEGER,
        Privacy TEXT,
        FOREIGN KEY(AdminID) REFERENCES users(ID)
    );`
    },
    //Members table
    {
      name: 'members',
      sql: `CREATE TABLE IF NOT EXISTS members (
        GroupID INTEGER,
        UserID TEXT,
        FOREIGN KEY(GroupID) REFERENCES groups(GroupID),
        FOREIGN KEY(UserID) REFERENCES users(ID)
    );`
    }
  ];

  try {
    // Create each table if it doesn't exist
    for (const {name, sql } of tables) {
      // Wait for the table to be created before continuing
      await createTable(sql);
    }
  } catch (err) {
    console.error('Error during database initialization:', err);
    throw err;
  }
}

module.exports = { initialiseDatabase, db };